package com.jeek.jdbc.myCode.homework1;

import com.jeek.jdbc.myCode.homeWork.utils.JDBCUtils;

import java.sql.*;
import java.util.Scanner;

public class CustomerDao {
    // 1. 插入客户数据
    public static void insertCustomer() {
        Connection conn = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入编号：");
        int id = scanner.nextInt();
        scanner.nextLine(); // 吸收换行符
        System.out.print("请输入姓名：");
        String name = scanner.nextLine();
        System.out.print("请输入邮箱：");
        String email = scanner.nextLine();
        System.out.print("请输入生日（格式：xxxx-xx-xx）：");
        String birthday = scanner.nextLine();

        String sql = "INSERT INTO customers (id, name, email, birthday) VALUES (?, ?, ?, ?)";

        try{
            conn = JDBCUtils.getConn();
            ppst = conn.prepareStatement(sql);
            ppst.setInt(1, id);
            ppst.setString(2, name);
            ppst.setString(3, email);
            ppst.setDate(4, Date.valueOf(birthday)); // 转换为SQL日期类型

            int rows = ppst.executeUpdate();
            if (rows > 0) {
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            System.out.println("插入失败：" + e.getMessage());
        }
    }

    // 2. 修改客户数据（按编号修改姓名）
    public static void updateCustomer() {
        Connection conn = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入待修改的客户编号：");
        int id = scanner.nextInt();
        scanner.nextLine(); // 吸收换行符
        System.out.print("请输入新的客户姓名：");
        String newName = scanner.nextLine();

        String sql = "UPDATE customers SET name = ? WHERE id = ?";
        try {
            conn = JDBCUtils.getConn();
            ppst = conn.prepareStatement(sql);

            ppst.setString(1, newName);
            ppst.setInt(2, id);

            int rows = ppst.executeUpdate();
            if (rows > 0) {
                System.out.println("修改成功！");
            } else {
                System.out.println("未找到该编号的客户！");
            }
        } catch (SQLException e) {
            System.out.println("修改失败：" + e.getMessage());
        }
    }

    // 3. 查询所有客户信息
    public static void queryAllCustomers() {
        Connection conn = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        String sql = "SELECT * FROM customers";
        try{
            conn = JDBCUtils.getConn();
            ppst = conn.prepareStatement(sql);
            rs = ppst.executeQuery();

            System.out.println("编号\t姓名\t邮箱\t\t生日");
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                System.out.printf("%d\t%s\t%s\t%s\n", id, name, email, birthday);
            }
        } catch (SQLException e) {
            System.out.println("查询失败：" + e.getMessage());
        }
    }

    // 4. 根据编号查询客户
    public static void queryCustomerById() {
        Connection conn = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入客户编号：");
        int id = scanner.nextInt();

        String sql = "SELECT * FROM customers WHERE id = ?";
        try{
            conn = JDBCUtils.getConn();
            ppst = conn.prepareStatement(sql);

            ppst.setInt(1, id);
            rs = ppst.executeQuery();

            System.out.println("编号\t姓名\t邮箱\t\t生日");
            if (rs.next()) {
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                System.out.printf("%d\t%s\t%s\t%s\n", id, name, email, birthday);
            } else {
                System.out.println("查无此客户！");
            }
        } catch (SQLException e) {
            System.out.println("查询失败：" + e.getMessage());
        }
    }

    // 5. 根据姓名查询客户
    public static void queryCustomerByName() {
        Connection conn = null;
        PreparedStatement ppst = null;
        ResultSet rs = null;
        Scanner scanner = new Scanner(System.in);
        System.out.print("请输入客户姓名：");
        String name = scanner.nextLine();

        String sql = "SELECT * FROM customers WHERE name = ?";
        try{
            conn = JDBCUtils.getConn();
            ppst = conn.prepareStatement(sql);

            ppst.setString(1, name);
            rs = ppst.executeQuery();

            System.out.println("编号\t姓名\t邮箱\t\t生日");
            boolean found = false;
            while (rs.next()) {
                found = true;
                int id = rs.getInt("id");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                System.out.printf("%d\t%s\t%s\t%s\n", id, name, email, birthday);
            }
            if (!found) {
                System.out.println("查无此客户！");
            }
        } catch (SQLException e) {
            System.out.println("查询失败：" + e.getMessage());
        }
    }
}
